
//增删改查函数集合
import java.sql.*;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;


public class CRUDfunc {


    public void addStudent( String url, String user, String password, JTextField studentIdField, JTextField nameField, JComboBox<String> sexComboBox, JTextField majorField, JTextField phoneNumberField) {

        // 获取用户输入的信息
        //getText() 用于从文本输入框中获取文本内容。 trim() 方法用于去除字符串前后的空格
        String studentId = studentIdField.getText().trim();
        String name = nameField.getText().trim();
        //getSelectedItem() 用于获取用户选择的下拉框中的选项。由于 getSelectedItem() 方法返回的是一个 Object 类型的对象，因此需要将其强制类型转换为 String 类型才能将其存储到名为 sex 的字符串变量中。
        String sex = (String) sexComboBox.getSelectedItem();
        String major = majorField.getText().trim();
        String phoneNumber = phoneNumberField.getText().trim();

        if ("".equals(studentId) || "".equals(name) || "".equals(major) || "".equals(phoneNumber)) {
            //弹出一个对话框，显示学号、姓名、专业和电话不能为空
            JOptionPane.showMessageDialog(null, "学号、姓名、专业和电话不能为空");
            return;
        }

        // 检查学号是否已经存在
        //数据库连接语句对象conn
        Connection conn = null;
        //预处理语句对象PreparedStatement
        PreparedStatement pstmt = null;
        //数据结果集对象rs
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(url, user, password);
            //占位符 ?，表示该位置需要一个参数值进行绑定
            String sql = "SELECT * FROM student_info WHERE student_id=?";
            //将查询语句 sql 绑定到预编译的SQL语句对象上，以备后续操作使用。
            pstmt = conn.prepareStatement(sql);
            //将 SQL 语句中的第一个占位符替换为 studentId 变量的值。
            pstmt.setString(1, studentId);
            //执行 SQL 查询语句。查询结果将被存储在 rs 变量中
            rs = pstmt.executeQuery();

            //next()判断查询结果中是否存在下一行记录
            if (rs.next()) {
                //对话框，提示学号已经存在，请重新输入，null 表示父组件，null表示这个对话框的父容器是空的，因此对话框将位于屏幕中央。
                JOptionPane.showMessageDialog(null, "学号已经存在，请重新输入");
                return;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // 插入学生信息
        try {
            conn = DriverManager.getConnection(url, user, password);
            String sql = "INSERT INTO student_info(student_id, name, major, sex, phone_number) VALUES(?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);
            pstmt.setString(2, name);
            pstmt.setString(3, major);
            pstmt.setString(4, sex);
            pstmt.setString(5, phoneNumber);
            //将执行的 SQL 语句影响的行数返回给Java程序,让程序知道数据库操作的结果
            pstmt.executeUpdate();

            // 提示信息
            JOptionPane.showMessageDialog(null, "学生信息插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public void modifyStudent(DefaultTableModel tableModel, String url, String user, String password, JTextField studentIdField, JTextField nameField, JComboBox<String> sexComboBox, JTextField majorField, JTextField phoneNumberField) {

        // 获取用户输入的信息
        String studentId = studentIdField.getText().trim();
        String name = nameField.getText().trim();
        String sex = (String) sexComboBox.getSelectedItem();
        String major = majorField.getText().trim();
        String phoneNumber = phoneNumberField.getText().trim();

        if ("".equals(studentId)) {
            JOptionPane.showMessageDialog(null, "学号不能为空");
            return;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DriverManager.getConnection(url, user, password);
            String sql = "update student_info set name=?,sex=?,major=?,phone_number=? where student_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setString(2,sex);
            pstmt.setString(3,major);
            pstmt.setString(4,phoneNumber);
            pstmt.setString(5,studentId);
            int result = pstmt.executeUpdate();
            if (result > 0) {
                // 修改表格中的数据
                //tableModel.getRowCount()获取 JTable 中的行数，
                for (int i = 0; i < tableModel.getRowCount(); i++) {
                    //判断studentId 和 JTable 的数据模型中特定行的第一个单元格的值是否相同
                    if (tableModel.getValueAt(i, 0).toString().equals(studentId)) {
                        String[] rowData = new String[]{studentId, name, sex, major, phoneNumber};
                        for (int j = 0; j < rowData.length; j++) {
                            //修改数据
                            tableModel.setValueAt(rowData[j], i, j);
                        }
                        JOptionPane.showMessageDialog(null,"学生信息修改成功");
                        break;
                    }
                }
            } else {
                JOptionPane.showMessageDialog(null,"学生信息修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }






    public void  deleStudent(DefaultTableModel tableModel,String url,String user,String password,JTable table){
        int row = table.getSelectedRow();
        if (row == -1){
            JOptionPane.showMessageDialog(null,"请选择一行数据");
            return;
        }
        //row 表示需要获取的行数，0 表示需要获取的列数
        String studentId = (String) tableModel.getValueAt(row,0);

        //删除学生信息
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DriverManager.getConnection(url,user,password);
            String sql = "delete from student_info where student_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,studentId);

            pstmt.executeUpdate();
            //tableModel.removeRow（）删除指定行
            tableModel.removeRow(row);
            JOptionPane.showMessageDialog(null,"学生信息删除成功");

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if (pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
    public void queryStudent(DefaultTableModel tableModel,String url,String user,String password,JTextField studentIdField){
        String studentId = studentIdField.getText().trim();
        if ("".equals(studentId)){
            JOptionPane.showMessageDialog(null,"学号不能为空");
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(url,user,password);
            String sql = "select * from student_info where student_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,studentId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                // 显示查询结果
                //将JTable中的所有行都清空，即将行数设置为0
                tableModel.setRowCount(0);
                String[] rowData = new String[] {rs.getString("student_id"), rs.getString("name"), rs.getString("sex"), rs.getString("major"), rs.getString("phone_number")};
                tableModel.addRow(rowData);
                JOptionPane.showMessageDialog(null,"查询成功");
            } else {
                // 提示信息
                JOptionPane.showMessageDialog(null, "学号不存在");
            }
        }catch (SQLException e){
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void queryAllStudent(DefaultTableModel tableModel, String url, String user, String password) {
        //清空页面
        tableModel.setRowCount(0);
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(url, user, password);

            String sql = "SELECT * FROM student_info";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            //rs.next()如果返回true，表示结果集中还有下一行记录
            while (rs.next()) {
                String[] rowData = new String[]{rs.getString("student_id"), rs.getString("name"), rs.getString("sex"), rs.getString("major"), rs.getString("phone_number")};
                tableModel.addRow(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}